# python 3.6
import matplotlib.pyplot as plt
import xlrd,xlwt
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import os

def is_float(str):
    try:
        float(str)
        return True
    except ValueError:
        return False

class ReadExcel(object):
    def __init__(self, data_path, sheetname,):
        #定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows

    def main(self,delayChart):
        data = []
        if delayChart == "totalCost":
            if self.sheetname == '控屏':
                for i in range(1, self.rowNum):
                    val = self.table.cell_value(i, 6)
                    if is_float(val):
                        data.append(int(val)/1000)
                        globalData.append(int(val)/1000)
                n, bins, patches = plt.hist(data, bins=20, edgecolor='black', histtype='bar')  # stepfilled  step bar
            elif self.sheetname == '闲聊':
                for i in range(1, self.rowNum):
                    val = self.table.cell_value(i, 6)
                    if is_float(val):
                        data.append(int(val)/1000)
                        globalData.append(int(val) / 1000)
                n, bins, patches = plt.hist(data, bins=20, edgecolor='black', histtype='bar')
            elif self.sheetname == '数据分析':
                for i in range(1, self.rowNum):
                    val = self.table.cell_value(i, 21)
                    if is_float(val):
                        data.append(int(val)/1000)
                        globalData.append(int(val) / 1000)
                n, bins, patches = plt.hist(data, bins=30, edgecolor='black', histtype='bar')
        # elif delayChart == "intentCost":
        #     for i in range(1, self.rowNum):
        #         if sheetname == '数据分析':
        #             val = self.table.cell_value(i, 15)
        #             if val.isdigit():
        #                 data.append(int(val)/1000)
        #         elif sheetname in ['控屏类指令','巡游','讲解']:
        #             val = self.table.cell_value(i, 11)
        #             if is_float(val):
        #                 data.append(val)
        #         elif sheetname == '闲聊':
        #             val = self.table.cell_value(i, 10)
        #             if is_float(val):
        #                 data.append(val)
        #     n, bins, patches = plt.hist(data, bins=10, edgecolor='black', histtype='bar')  # stepfilled  step bar
        #     # n 为 各个bin里面的计数; bins 为 bin 的边界值组成的 列表; patches 为 图形
        # elif delayChart == "genSqlCost":
        #     for i in range(1, self.rowNum):
        #         val = self.table.cell_value(i, 16)
        #         if val.isdigit():
        #             data.append(int(val)/1000)
        #     n, bins, patches = plt.hist(data, bins=15, edgecolor='black', histtype='bar')  # stepfilled  step bar
        # elif delayChart == "execSqlCost":
        #     for i in range(1, self.rowNum):
        #         val = self.table.cell_value(i, 17)
        #         if val.isdigit():
        #             data.append(int(val)/1000)
        #     n, bins, patches = plt.hist(data, bins=10, edgecolor='black', histtype='bar')  # stepfilled  step bar
        # elif delayChart == "genChatCost":
        #     for i in range(1, self.rowNum):
        #         val = self.table.cell_value(i, 18)
        #         if val.isdigit():
        #             data.append(int(val)/1000)
        #     n, bins, patches = plt.hist(data, bins=20, edgecolor='black', histtype='bar')  # stepfilled  step bar
        arr = np.array(data)
        percentile_80 = np.percentile(arr, 80)
        percentile_90 = np.percentile(arr, 90)
        print('{}时延最小值，最大值，P80, P90, 平均值：'.format(self.sheetname),  min(data), max(data), percentile_80, percentile_90, sum(data) / len(data))

       # hist(x, bins=None, range=None, density=False, weights=None, cumulative=False, bottom=None, histtype='bar',
        #      align='mid', orientation='vertical', rwidth=None, log=False, color=None, label=None, stacked=False, *,
        #      data=None, **kwargs)
        # 使用matplotlib的hist函数绘制直方图
        # 每个柱状图标注数量
        for i in range(len(n)):
            plt.text(bins[i] + (bins[1] - bins[0]) / 2, n[i] * 1.01, int(n[i]), ha='center', va='bottom')
        # 添加网格
        plt.grid()
        # 添加刻度
        # min_1 = self.move["Rating"].min()
        # max_1 = self.move["Rating"].max()
        # t1 = np.linspace(min_1, max_1, num=11)
        # plt.xticks(t1)

        from matplotlib.font_manager import FontProperties
        # 设置中文字体，fname是我的电脑中的字体的路径
        font = FontProperties(fname='/System/Library/Fonts/STHeiti Medium.ttc', size=10)

        # plt.title('未在库中的{}指令{}'.format(sheetname,cateDict[delayChart]),FontProperties=font)
        plt.title('{}{}'.format(sheetname,cateDict[delayChart]),FontProperties=font)
        plt.xlabel('延时 (s)',FontProperties=font)
        plt.ylabel('出现次数',FontProperties=font)

        plt.show()



if __name__ == '__main__':
    data_path = '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/history/端到端-23.11.22/'

    globalData = []
    delayChart = 'totalCost'
    # delayChart = 'intentCost'
    # delayChart = 'genSqlCost'
    # delayChart = 'execSqlCost'
    # delayChart = 'genChatCost'

    cateDict = {'intentCost':'意图识别耗时','totalCost':'总耗时','genSqlCost':'获取sql耗时',
                'execSqlCost':'执行sql耗时','genChatCost':'获取图表和播报耗时'}


    files_list = os.listdir(data_path)
    for file_name in files_list:
        if not file_name.startswith('.~') and file_name.endswith('控屏_复测.xlsx') or file_name.endswith('控屏_复测.xls'):
            sheetname = '控屏'
            read_obj = ReadExcel(data_path + file_name, sheetname)
            read_obj.main(delayChart)

        if not file_name.startswith('.~') and file_name.endswith('闲聊.xlsx') or file_name.endswith('闲聊.xls'):
            sheetname = '闲聊'
            read_obj = ReadExcel(data_path + file_name, sheetname)
            read_obj.main(delayChart)

        if not file_name.startswith('.~') and file_name.endswith('数据分析复测.xlsx') or file_name.endswith('数据分析复测.xls'):
            sheetname = '数据分析'
            read_obj = ReadExcel(data_path + file_name, sheetname)
            read_obj.main(delayChart)

    globalArr = np.array(globalData)
    globalPercentile_80 = np.percentile(globalArr, 80)
    globalPercentile_90 = np.percentile(globalArr, 90)
    print('汇总-时延最小值，最大值，P80, P90, 平均值：', min(globalData), max(globalData), globalPercentile_80, globalPercentile_90,
          sum(globalData) / len(globalData))
